package edu.ccut.saturn.studio.database.model;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.eclipse.core.runtime.IProgressMonitor;
import org.eclipse.core.runtime.IStatus;
import org.eclipse.core.runtime.Status;
import org.eclipse.core.runtime.jobs.Job;
import org.eclipse.swt.graphics.Image;
import org.eclipse.ui.IActionFilter;

import edu.ccut.saturn.studio.Activator;
import edu.ccut.saturn.studio.Images;
import edu.ccut.saturn.studio.core.util.eclipse.Messages;
import edu.ccut.saturn.studio.database.adapters.DatabaseAdapterFactory;
import edu.ccut.saturn.studio.database.adapters.IDatabaseAdapter;
import edu.ccut.saturn.studio.database.ui.DriverJarUtil;

/**
 * 描述一个有效的数据库连接对象
 * 
 * @author 谷钰
 * @version 1.0 2008.12.02
 */
public class DBConnection extends SaturnTree implements IActionFilter {

	private String type = "";

	private String driverPath = "";

	private String driverClassName = "";

	private String url = "";

	private String username = "";

	private String password = "";

	private List<SqlTable> tables = new ArrayList<SqlTable>();

	private Connection connection = null;

	public static interface DBConnectionListener {
		void onGetConnection(Connection connection);
	}

	public DBConnection(String connectionName) {
		super(connectionName);
	}

	public String getDatabaseName() {
		return this.getAdapter().getDatabaseName(this);
	}

	@Override
	public void expand() throws Exception {
		if (super.hasChildren()) {
			this.removeChildren();
		}

		List<String> schemaNames = getSchemaNames();

		if (schemaNames != null) {
			for (String name : schemaNames) {
				SqlSchema schema = new SqlSchema(name, DBConnection.this);

				addChild(schema);
			}
		}

		setOpen(true);
	}

	@Override
	public Image getImage() {
		return Activator.getImage(Images.DATABASE_CONNECTION);
	}

	public List<SqlTable> getTables() {
		return tables;
	}

	public void setTables(List<SqlTable> tables) {
		this.tables = tables;
	}

	public void getConnectionAsync(final DBConnectionListener listener) {
		Job job = new Job(Messages
				.getString("DBConnection.connectingpleasewait")) { //$NON-NLS-1$
			protected IStatus run(IProgressMonitor monitor) {
				Connection connection = null;

				try {
					connection = getConnection();
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					if (listener != null) {
						listener.onGetConnection(connection);
					}
				}
				return Status.OK_STATUS;
			}
		};
		job.schedule();
	}

	public Connection getConnectionSync() throws Exception {
		return this.getConnection();
	}

	// private Connection getConnection() throws Exception {
	// if (this.connection == null || this.connection.isClosed()) {
	// Class.forName(driverClassName);
	//			
	// this.connection = DriverManager.getConnection(url, username, password);
	// }
	// return this.connection;
	// }

	private Connection getConnection() throws Exception {

		if (this.connection == null || this.connection.isClosed()) {
			Driver driver = DriverJarUtil.loadDriver(driverPath,
					driverClassName);

			Properties properties = new Properties();
			properties.put("user", username);
			properties.put("password", password);

			this.connection = driver.connect(url, properties);
		}

		return this.connection;

	}

	public void closeConnection() throws Exception {
		if (this.connection != null) {
			if (!this.connection.isClosed()) {
				this.connection.close();
			}
			this.connection = null;
		}
	}

	public boolean isConnected() {
		return this.connection != null;
	}

	public List<String> getSequenceNames() throws Exception {
		List<String> sequenceList = new ArrayList<String>();

		Statement statement = null;
		ResultSet resultSet = null;

		if (this.getConnection() != null) {
			try {
				statement = connection.createStatement();
				if (type.equals("Oracle") || type.equals("SQLServer")
						|| type.equals("MySQL") || type.equals("DB2")) {
					resultSet = statement
							.executeQuery("select sequence_name from user_sequences");
					while (resultSet.next()) {
						sequenceList.add(resultSet.getString("sequence_name"));
					}
				}
			} finally {
				try {
					if (statement != null) {
						statement.close();
					}

					if (resultSet != null) {
						resultSet.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return sequenceList;
	}

	/**
	 * 
	 * @param schemaName
	 *            所在实例名称
	 * @param tableName
	 *            表名
	 * @return
	 * @throws Exception
	 */
	public List<SqlColumn> getTableColumns(String schemaName, String tableName)
			throws Exception {
		schemaName = schemaName.toUpperCase();
		List<SqlColumn> columns = new ArrayList<SqlColumn>();

		if (this.getConnection() != null) {
			DatabaseMetaData dbmd = connection.getMetaData();

			ResultSet fieldRts = null;
			ResultSet primerKeyRts = null;

			tableName = getAdapter().filterTableName(schemaName, tableName);

			try {
				fieldRts = this.getAdapter().getColumns(dbmd, "", schemaName,
						tableName, null);

				while (fieldRts.next()) {
					String name = fieldRts.getString(4);
					String type = fieldRts.getString(6);

					if (type.contains(" identity")) {
						type = type.replace(" identity", "");
					}
					int size = fieldRts.getInt(7);

					SqlColumn column = new SqlColumn(name);
					column.setTableName(tableName);
					column.setColumnDataType(type);
					column.setColumnLenth(size);

					columns.add(column);
				}

				primerKeyRts = this.getAdapter().getPrimerKey(dbmd, "",
						schemaName, tableName);

				while (primerKeyRts.next()) {
					String name = primerKeyRts.getString(4);
					short primaryKeyOrder = primerKeyRts.getShort(5);

					if (columns != null) {
						for (SqlColumn column : columns) {
							if (name != null && name.equals(column.getName())) {
								column.setPrimaryKeyOrder(primaryKeyOrder);
							}
						}
					}
				}
			} finally {
				if (fieldRts != null) {
					fieldRts.close();
				}

				if (primerKeyRts != null) {
					primerKeyRts.close();
				}
			}
		}

		return columns;
	}

	public List<String> getSchemaNames() throws Exception {
		List<String> schemaNames = new ArrayList<String>();

		if (this.getConnection() != null) {
			DatabaseMetaData dbmd = this.connection.getMetaData();
			ResultSet schemaRts = null;

			try {
				schemaNames = this.getAdapter().getSchemas(dbmd, schemaRts,
						schemaNames);
			} finally {
				if (schemaRts != null) {
					schemaRts.close();
				}
			}
		}
		return schemaNames;
	}

	public List<String> getTableNames(String schemaName) throws Exception {

		return this.getNames(schemaName, "TABLE");
	}

	public List<String> getViewNames(String schemaName) throws Exception {

		return this.getNames(schemaName, "VIEW");
	}

	private List<String> getNames(String schemaName, String type)
			throws Exception {

		List<String> names = new ArrayList<String>();

		if (this.getConnection() != null) {
			DatabaseMetaData dbmd = this.connection.getMetaData();

			ResultSet resultSet = null;
			try {
				names = this.getAdapter().getTables(dbmd, "", schemaName, "%",
						type, resultSet);
			} finally {
				if (resultSet != null) {
					resultSet.close();
				}
			}
		}

		return names;
	}

	public String getType() {
		return type;
	}

	public void setType(String dbType) {
		this.type = dbType;
	}

	public String getDriverPath() {
		return driverPath;
	}

	public void setDriverPath(String driverPath) {
		this.driverPath = driverPath;
	}

	public String getDriverClassName() {
		return driverClassName;
	}

	public void setDriverClassName(String driverClassName) {
		this.driverClassName = driverClassName;
	}

	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String userid) {
		this.username = userid;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public boolean testAttribute(Object target, String name, String value) {
		String isOpen = "closed";

		if (super.isOpen()) {
			isOpen = "open";
		}

		return (target instanceof DBConnection) && name.equals("isOpen")
				&& isOpen.equalsIgnoreCase(value);
	}

	public IDatabaseAdapter getAdapter() {
		return DatabaseAdapterFactory.getInstance().getAdapter(this.getType());
	}

	public String getDescription() {
		StringBuffer stringbuffer = new StringBuffer();
		stringbuffer.append("<b>").append("").append("</b>");
		stringbuffer.append("<p>").append(getName());
		stringbuffer.append("<br><b>").append("").append("</b>");
		stringbuffer.append("<p>").append(getUsername());
		stringbuffer.append("<br><b>").append("").append("</b>");
		stringbuffer.append("<p>").append(getUrl());
		return stringbuffer.toString();
	}
}
